﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace _065
{
    /// <summary>
    /// 使用控制台应用程序编写增删查改操作
    /// </summary>
    public  class Program
    {
        static void Main(string[] args)
        {
            //sql连接字符串
            var adoNet = new AdoNet(".", "MyDatabaseOne", "sa", "35683568");
            //调用查询方法
            var sqlstr = "select * from users";
            adoNet.AdoNetSate(sqlstr);
            //增加
            /*
            string strsql = "insert into users(code,name,pwd) values(4,'黄记','016676')";
            adoNet.AdoNetAdd(strsql);
            */
            //删除
            /*
            string strsql = "delete from users where code=4";
            adoNet.AdoDelete(strsql);
            */
            //修改
            /*
            string strsql = "Update users set name='黄6' where code=3";
            adoNet.AdoEdit(strsql);
            */
            Console.ReadKey();
        }
    }
    public class AdoNet
    {
        //声明连接字符串
        public string Sqlstr = "data source={0};database={1};User id={2};pwd={3}";
        //public SqlConnection Conn = new SqlConnection("server=.;database=MyDatabaseOne;user=sa;pwd=35683568");
        //声明ADO类
        public SqlCommand Command;
        public SqlConnection Conn;
        //声明构造函数，传入连接字符串进行拼接
        public AdoNet(string datasource,string database,string userid,string pwd)
        {
            var cStr = string.Format(Sqlstr, datasource, database, userid, pwd);
            Conn = new SqlConnection(cStr);
        }
        //------------增加
        public void AdoNetAdd(string cmd)
        {
            //打开数据库
            Conn.Open();
            //使用using在命令执行完后会释放资源
            using (SqlCommand slqcmd=new SqlCommand(cmd,Conn))
            {
                //获取要执行的sql语句
                slqcmd.CommandText = cmd;
                if(slqcmd.ExecuteNonQuery()>0)
                {
                    Console.WriteLine("增加成功");
                }
                else
                {
                    Console.WriteLine("增加失败");
                }
            }
        }
        //------------删除
        public void AdoDelete(string cmd)
        {
            Conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(cmd, Conn))
            {
                sqlcmd.CommandText = cmd;
                if(sqlcmd.ExecuteNonQuery()>0)
                {
                    Console.WriteLine("删除成功");
                }
                else
                {
                    Console.WriteLine("删除失败");
                }
            }
        }
        //------------查询
        public void AdoNetSate(string cmd)
        {
            Conn.Open();
            Command = new SqlCommand(cmd, Conn);
            //批量查询
            /*
            SqlDataAdapter ad = new SqlDataAdapter(Command);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            var s1 = dt.Select();
            var arrlist1 = s1[0].ItemArray[0];
            var arrlist2 = s1[0].ItemArray[1];
            var arrlist3 = s1[0].ItemArray[0];
            var arrlist4 = s1[0].ItemArray[1];
            Console.WriteLine(arrlist1);
            Console.WriteLine(arrlist2);
            Console.WriteLine(arrlist3);
            Console.WriteLine(arrlist4);
            */
            //单条查询
          
            SqlDataReader dataAdapter = Command.ExecuteReader();
            while (dataAdapter.Read())
            {
                Console.WriteLine("编号：{0},\t姓名：{1},\t密码:{2}", dataAdapter["code"], dataAdapter["name"], dataAdapter["pwd"]); 
            }
          
            Console.ReadKey();
        }
        //------------修改
        public void AdoEdit(string cmd)
        {
            Conn.Open();
            using (SqlCommand sqlcmd = new SqlCommand(cmd, Conn))
            {
                sqlcmd.CommandText = cmd;
                if(sqlcmd.ExecuteNonQuery()>0)
                {
                    Console.WriteLine("修改成功");
                }
                else
                {
                    Console.WriteLine("修改失败");
                }
            }
        }

    }
}
